import sqlite3
import logging
from typing import Optional, Dict, List, Any, Tuple

# 配置日志记录
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

class SQLiteCRUD:
    def __init__(self, db_path: str):
        self.db_path = db_path
        self.conn: Optional[sqlite3.Connection] = None
        self._is_active = False  # 跟踪事务状态

    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    def connect(self) -> None:
        """建立数据库连接并启用字典结果"""
        try:
            self.conn = sqlite3.connect(self.db_path)
            self.conn.row_factory = sqlite3.Row  # 结果返回字典格式
            self.conn.execute("PRAGMA foreign_keys = ON")  # 启用外键约束
            logging.info(f"Connected to database: {self.db_path}")
        except sqlite3.Error as e:
            logging.error(f"Connection failed: {str(e)}")
            raise

    def close(self) -> None:
        """安全关闭数据库连接"""
        if self.conn:
            try:
                if self._is_active:
                    self.conn.rollback()
                self.conn.close()
                logging.info("Database connection closed")
            except sqlite3.Error as e:
                logging.error(f"Closing connection failed: {str(e)}")
                raise

    def execute(
        self,
        sql: str,
        params: Optional[Tuple[Any, ...]] = None,
        commit: bool = False
    ) -> sqlite3.Cursor:
        """
        通用执行方法（核心方法）
        :param sql: SQL语句（带?占位符）
        :param params: 参数元组
        :param commit: 是否立即提交
        :return: Cursor对象
        """
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql, params or ())
            if commit:
                self.conn.commit()
                self._is_active = False
            else:
                self._is_active = True
            return cursor
        except sqlite3.Error as e:
            logging.error(f"SQL execution failed: {str(e)}\nSQL: {sql}")
            self.conn.rollback()
            self._is_active = False
            raise

    def create_table(self, table_name: str, schema: Dict[str, str]) -> None:
        """
        创建数据表
        :param table_name: 表名
        :param schema: 字段定义字典（字段名: 类型约束）
        """
        columns = ", ".join([f"{k} {v}" for k, v in schema.items()])
        sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns})"
        self.execute(sql, commit=True)
        logging.info(f"Table created/verified: {table_name}")

    def insert(
        self,
        table_name: str,
        data: Dict[str, Any],
        commit: bool = True
    ) -> int:
        """
        插入数据
        :param table_name: 表名
        :param data: 数据字典（字段: 值）
        :param commit: 是否立即提交
        :return: 插入行的rowid
        """
        fields = ", ".join(data.keys())
        placeholders = ", ".join(["?"] * len(data))
        sql = f"INSERT INTO {table_name} ({fields}) VALUES ({placeholders})"
        cursor = self.execute(sql, tuple(data.values()), commit=commit)
        return cursor.lastrowid

    def select(
        self,
        table_name: str,
        columns: str = "*",
        where: Optional[str] = None,
        params: Optional[Tuple[Any, ...]] = None
    ) -> List[Dict[str, Any]]:
        """
        查询数据
        :param table_name: 表名
        :param columns: 查询字段（默认全部）
        :param where: WHERE条件（带?占位符）
        :param params: 条件参数
        :return: 结果字典列表
        """
        sql = f"SELECT {columns} FROM {table_name}"
        if where:
            sql += f" WHERE {where}"
        cursor = self.execute(sql, params)
        return [dict(row) for row in cursor.fetchall()]

    def update(
        self,
        table_name: str,
        data: Dict[str, Any],
        where: str,
        params: Tuple[Any, ...],
        commit: bool = True
    ) -> int:
        """
        更新数据
        :param table_name: 表名
        :param data: 更新数据字典
        :param where: WHERE条件（带?占位符）
        :param params: 条件参数
        :param commit: 是否立即提交
        :return: 影响的行数
        """
        set_clause = ", ".join([f"{k} = ?" for k in data.keys()])
        sql = f"UPDATE {table_name} SET {set_clause} WHERE {where}"
        values = tuple(data.values()) + params
        cursor = self.execute(sql, values, commit=commit)
        return cursor.rowcount

    def delete(
        self,
        table_name: str,
        where: str,
        params: Tuple[Any, ...],
        commit: bool = True
    ) -> int:
        """
        删除数据
        :param table_name: 表名
        :param where: WHERE条件（带?占位符）
        :param params: 条件参数
        :param commit: 是否立即提交
        :return: 影响的行数
        """
        sql = f"DELETE FROM {table_name} WHERE {where}"
        cursor = self.execute(sql, params, commit=commit)
        return cursor.rowcount

    def commit(self) -> None:
        """手动提交事务"""
        try:
            self.conn.commit()
            self._is_active = False
        except sqlite3.Error as e:
            logging.error(f"Commit failed: {str(e)}")
            self.conn.rollback()
            raise
